In [10]:
import sqlite3
import pandas as pd
conn = sqlite3.connect("nominations.db")
schema = conn.execute("pragma table_info(nominations);")
first_ten = conn.execute("SELECT * FROM nominations LIMIT 10;")
for row in schema:
print(row)
for row in first_ten:
print(row)
(0, u'Year', u'INTEGER', 0, None, 0)
(1, u'Category', u'TEXT', 0, None, 0)
(2, u'Nominee', u'TEXT', 0, None, 0)
(3, u'Won?', u'INTEGER', 0, None, 0)
(4, u'Movie', u'TEXT', 0, None, 0)
(5, u'Character', u'TEXT', 0, None, 0)
(2010, u'Actor -- Leading Role', u'Javier Bardem', 0, u'Biutiful', u'Uxbal')
(2010, u'Actor -- Leading Role', u'Jeff Bridges', 0, u'True Grit', u'Rooster Cogburn')
(2010, u'Actor -- Leading Role', u'Jesse Eisenberg', 0, u'The Social Network', u'Mark Zuckerberg')
(2010, u'Actor -- Leading Role', u'Colin Firth', 1, u"The King's Speech", u'King George VI')
(2010, u'Actor -- Leading Role', u'James Franco', 0, u'127 Hours', u'Aron Ralston')
(2010, u'Actor -- Supporting Role', u'Christian Bale', 1, u'The Fighter', u'Dicky Eklund')
(2010, u'Actor -- Supporting Role', u'John Hawkes', 0, u"Winter's Bone", u'Teardrop')
(2010, u'Actor -- Supporting Role', u'Jeremy Renner', 0, u'The Town', u'James Coughlin')
(2010, u'Actor -- Supporting Role', u'Mark Ruffalo', 0, u'The Kids Are All Right', u'Paul')
(2010, u'Actor -- Supporting Role', u'Geoffrey Rush', 0, u"The King's Speech", u'Lionel Logue')
In [16]:
years_hosts = [(2010, "Steve Martin"),
(2009, "Hugh Jackman"),
(2008, "Jon Stewart"),
(2007, "Ellen DeGeneres"),
(2006, "Jon Stewart"),
(2005, "Chris Rock"),
(2004, "Billy Crystal"),
(2003, "Steve Martin"),
(2002, "Whoopi Goldberg"),
(2001, "Steve Martin"),
(2000, "Billy Crystal"),
]
create_query = "CREATE TABLE ceremonies ( \
id integer PRIMARY KEY, \
Year integer, \
Host text);"
conn.execute(create_query)
insert_query = "INSERT INTO ceremonies (Year, Host) VALUES (?, ?);"
conn.executemany(insert_query, years_hosts)
Out[16]:
<sqlite3.Cursor at 0x7d3da40>
In [17]:
for row in conn.execute("SELECT * FROM ceremonies;"):
print(row)
(1, 2010, u'Steve Martin')
(2, 2009, u'Hugh Jackman')
(3, 2008, u'Jon Stewart')
(4, 2007, u'Ellen DeGeneres')
(5, 2006, u'Jon Stewart')
(6, 2005, u'Chris Rock')
(7, 2004, u'Billy Crystal')
(8, 2003, u'Steve Martin')
(9, 2002, u'Whoopi Goldberg')
(10, 2001, u'Steve Martin')
(11, 2000, u'Billy Crystal')
In [19]:
conn.execute("PRAGMA foreign_keys = ON;")
Out[19]:
<sqlite3.Cursor at 0x7d3dce0>
In [59]:
query = """SELECT ceremonies.id, nominations.category, nominations.nominee, nominations.movie, nominations.character, nominations."won?"
FROM nominations
INNER JOIN ceremonies ON
nominations.year == ceremonies.year
;"""
results = conn.execute(query).fetchall()
for row in results:
print(row)
(1, u'Actor -- Leading Role', u'Javier Bardem', u'Biutiful', u'Uxbal', 0)
(1, u'Actor -- Leading Role', u'Jeff Bridges', u'True Grit', u'Rooster Cogburn', 0)
(1, u'Actor -- Leading Role', u'Jesse Eisenberg', u'The Social Network', u'Mark Zuckerberg', 0)
(1, u'Actor -- Leading Role', u'Colin Firth', u"The King's Speech", u'King George VI', 1)
(1, u'Actor -- Leading Role', u'James Franco', u'127 Hours', u'Aron Ralston', 0)
(1, u'Actor -- Supporting Role', u'Christian Bale', u'The Fighter', u'Dicky Eklund', 1)
(1, u'Actor -- Supporting Role', u'John Hawkes', u"Winter's Bone", u'Teardrop', 0)
(1, u'Actor -- Supporting Role', u'Jeremy Renner', u'The Town', u'James Coughlin', 0)
(1, u'Actor -- Supporting Role', u'Mark Ruffalo', u'The Kids Are All Right', u'Paul', 0)
(1, u'Actor -- Supporting Role', u'Geoffrey Rush', u"The King's Speech", u'Lionel Logue', 0)
(1, u'Actress -- Leading Role', u'Annette Bening', u'The Kids Are All Right', u'Nic', 0)
(1, u'Actress -- Leading Role', u'Nicole Kidman', u'Rabbit Hole', u'Becca', 0)
(1, u'Actress -- Leading Role', u'Jennifer Lawrence', u"Winter's Bone", u'Ree', 0)
(1, u'Actress -- Leading Role', u'Natalie Portman', u'Black Swan', u'Nina Sayers/The Swan Queen', 1)
(1, u'Actress -- Leading Role', u'Michelle Williams', u'Blue Valentine', u'Cindy', 0)
(1, u'Actress -- Supporting Role', u'Amy Adams', u'The Fighter', u'Charlene Fleming', 0)
(1, u'Actress -- Supporting Role', u'Helena Bonham Carter', u"The King's Speech", u'Queen Elizabeth', 0)
(1, u'Actress -- Supporting Role', u'Melissa Leo', u'The Fighter', u'Alice Ward', 1)
(1, u'Actress -- Supporting Role', u'Hailee Steinfeld', u'True Grit', u'Mattie Ross', 0)
(1, u'Actress -- Supporting Role', u'Jacki Weaver', u'Animal Kingdom', u"Janine 'Smurf' Cody", 0)
(2, u'Actor -- Leading Role', u'Jeff Bridges', u'Crazy Heart', u'Bad Blake', 1)
(2, u'Actor -- Leading Role', u'George Clooney', u'Up in the Air', u'Ryan Bingham', 0)
(2, u'Actor -- Leading Role', u'Colin Firth', u'A Single Man', u'George', 0)
(2, u'Actor -- Leading Role', u'Morgan Freeman', u'Invictus', u'Nelson Mandela', 0)
(2, u'Actor -- Leading Role', u'Jeremy Renner', u'The Hurt Locker', u'Staff Sergeant William James', 0)
(2, u'Actor -- Supporting Role', u'Matt Damon', u'Invictus', u'Francois Pienaar', 0)
(2, u'Actor -- Supporting Role', u'Woody Harrelson', u'The Messenger', u'Captain Tony Stone', 0)
(2, u'Actor -- Supporting Role', u'Christopher Plummer', u'The Last Station', u'Tolstoy', 0)
(2, u'Actor -- Supporting Role', u'Stanley Tucci', u'The Lovely Bones', u'George Harvey', 0)
(2, u'Actor -- Supporting Role', u'Christoph Waltz', u'Inglourious Basterds', u'Col. Hans Landa', 1)
(2, u'Actress -- Leading Role', u'Sandra Bullock', u'The Blind Side', u'Leigh Anne Tuohy', 1)
(2, u'Actress -- Leading Role', u'Helen Mirren', u'The Last Station', u'Sofya', 0)
(2, u'Actress -- Leading Role', u'Carey Mulligan', u'An Education', u'Jenny', 0)
(2, u'Actress -- Leading Role', u'Gabourey Sidibe', u"Precious: Based on the Novel 'Push' by Sapphire", u'Precious', 0)
(2, u'Actress -- Leading Role', u'Meryl Streep', u'Julie & Julia', u'Julia Child', 0)
(2, u'Actress -- Supporting Role', u'Pen\xe9lope Cruz', u'Nine', u'Carla', 0)
(2, u'Actress -- Supporting Role', u'Vera Farmiga', u'Up in the Air', u'Alex Goran', 0)
(2, u'Actress -- Supporting Role', u'Maggie Gyllenhaal', u'Crazy Heart', u'Jean Craddock', 0)
(2, u'Actress -- Supporting Role', u'Anna Kendrick', u'Up in the Air', u'Natalie Keener', 0)
(2, u'Actress -- Supporting Role', u"Mo'Nique", u"Precious: Based on the Novel 'Push' by Sapphire", u'Mary', 1)
(3, u'Actor -- Leading Role', u'Richard Jenkins', u'The Visitor', u'Walter', 0)
(3, u'Actor -- Leading Role', u'Frank Langella', u'Frost/Nixon', u'Richard Nixon', 0)
(3, u'Actor -- Leading Role', u'Sean Penn', u'Milk', u'Harvey Milk', 1)
(3, u'Actor -- Leading Role', u'Brad Pitt', u'The Curious Case of Benjamin Button', u'Benjamin Button', 0)
(3, u'Actor -- Leading Role', u'Mickey Rourke', u'The Wrestler', u'Randy', 0)
(3, u'Actor -- Supporting Role', u'Josh Brolin', u'Milk', u'Dan White', 0)
(3, u'Actor -- Supporting Role', u'Robert Downey Jr.', u'Tropic Thunder', u'Kirk Lazarus', 0)
(3, u'Actor -- Supporting Role', u'Philip Seymour Hoffman', u'Doubt', u'Father Brendan Flynn', 0)
(3, u'Actor -- Supporting Role', u'Heath Ledger', u'The Dark Knight', u'Joker', 1)
(3, u'Actor -- Supporting Role', u'Michael Shannon', u'Revolutionary Road', u'John Givings', 0)
(3, u'Actress -- Leading Role', u'Anne Hathaway', u'Rachel Getting Married', u'Kym', 0)
(3, u'Actress -- Leading Role', u'Angelina Jolie', u'Changeling', u'Christine Collins', 0)
(3, u'Actress -- Leading Role', u'Melissa Leo', u'Frozen River', u'Ray Eddy', 0)
(3, u'Actress -- Leading Role', u'Meryl Streep', u'Doubt', u'Sister Aloysius Beauvier', 0)
(3, u'Actress -- Leading Role', u'Kate Winslet', u'The Reader', u'Hanna Schmitz', 1)
(3, u'Actress -- Supporting Role', u'Amy Adams', u'Doubt', u'Sister James', 0)
(3, u'Actress -- Supporting Role', u'Pen\xe9lope Cruz', u'Vicky Cristina Barcelona', u'Maria Elena', 1)
(3, u'Actress -- Supporting Role', u'Viola Davis', u'Doubt', u'Mrs. Miller', 0)
(3, u'Actress -- Supporting Role', u'Taraji P. Henson', u'The Curious Case of Benjamin Button', u'Queenie', 0)
(3, u'Actress -- Supporting Role', u'Marisa Tomei', u'The Wrestler', u'Cassidy', 0)
(4, u'Actor -- Leading Role', u'George Clooney', u'Michael Clayton', u'Michael Clayton', 0)
(4, u'Actor -- Leading Role', u'Daniel Day-Lewis', u'There Will Be Blood', u'Daniel Plainview', 1)
(4, u'Actor -- Leading Role', u'Johnny Depp', u'Sweeney Todd The Demon Barber of Fleet Street', u'Sweeney Todd', 0)
(4, u'Actor -- Leading Role', u'Tommy Lee Jones', u'In the Valley of Elah', u'Hank Deerfield', 0)
(4, u'Actor -- Leading Role', u'Viggo Mortensen', u'Eastern Promises', u'Nikolai', 0)
(4, u'Actor -- Supporting Role', u'Casey Affleck', u'The Assassination of Jesse James by the Coward Robert Ford', u'Robert Ford', 0)
(4, u'Actor -- Supporting Role', u'Javier Bardem', u'No Country for Old Men', u'Anton Chigurh', 1)
(4, u'Actor -- Supporting Role', u'Philip Seymour Hoffman', u"Charlie Wilson's War", u'Gust Avrakotos', 0)
(4, u'Actor -- Supporting Role', u'Hal Holbrook', u'Into the Wild', u'Ron Franz', 0)
(4, u'Actor -- Supporting Role', u'Tom Wilkinson', u'Michael Clayton', u'Arthur Edens', 0)
(4, u'Actress -- Leading Role', u'Cate Blanchett', u'Elizabeth: The Golden Age', u'Queen Elizabeth I', 0)
(4, u'Actress -- Leading Role', u'Julie Christie', u'Away from Her', u'Fiona', 0)
(4, u'Actress -- Leading Role', u'Marion Cotillard', u'La Vie en Rose', u'Edith Piaf', 1)
(4, u'Actress -- Leading Role', u'Laura Linney', u'The Savages', u'Wendy Savage', 0)
(4, u'Actress -- Leading Role', u'Ellen Page', u'Juno', u'Juno MacGuff', 0)
(4, u'Actress -- Supporting Role', u'Cate Blanchett', u"I'm Not There", u'Jude', 0)
(4, u'Actress -- Supporting Role', u'Ruby Dee', u'American Gangster', u'Mama Lucas', 0)
(4, u'Actress -- Supporting Role', u'Saoirse Ronan', u'Atonement', u'Briony Tallis, aged 13', 0)
(4, u'Actress -- Supporting Role', u'Amy Ryan', u'Gone Baby Gone', u'Helene McCready', 0)
(4, u'Actress -- Supporting Role', u'Tilda Swinton', u'Michael Clayton', u'Karen Crowder', 1)
(5, u'Actor -- Leading Role', u'Leonardo DiCaprio', u'Blood Diamond', u'Danny Archer', 0)
(5, u'Actor -- Leading Role', u'Ryan Gosling', u'Half Nelson', u'Dan Dunne', 0)
(5, u'Actor -- Leading Role', u"Peter O'Toole", u'Venus', u'Maurice', 0)
(5, u'Actor -- Leading Role', u'Will Smith', u'The Pursuit of Happyness', u'Chris Gardner', 0)
(5, u'Actor -- Leading Role', u'Forest Whitaker', u'The Last King of Scotland', u'Idi Amin', 1)
(5, u'Actor -- Supporting Role', u'Alan Arkin', u'Little Miss Sunshine', u'Grandpa', 1)
(5, u'Actor -- Supporting Role', u'Jackie Earle Haley', u'Little Children', u'Ronnie J. McGorvey', 0)
(5, u'Actor -- Supporting Role', u'Djimon Hounsou', u'Blood Diamond', u'Solomon Vandy', 0)
(5, u'Actor -- Supporting Role', u'Eddie Murphy', u'Dreamgirls', u"James 'Thunder' Early", 0)
(5, u'Actor -- Supporting Role', u'Mark Wahlberg', u'The Departed', u'Dignam', 0)
(5, u'Actress -- Leading Role', u'Pen\xe9lope Cruz', u'Volver', u'Raimunda', 0)
(5, u'Actress -- Leading Role', u'Judi Dench', u'Notes on a Scandal', u'Barbara Covett', 0)
(5, u'Actress -- Leading Role', u'Helen Mirren', u'The Queen', u'The Queen', 1)
(5, u'Actress -- Leading Role', u'Meryl Streep', u'The Devil Wears Prada', u'Miranda Priestly', 0)
(5, u'Actress -- Leading Role', u'Kate Winslet', u'Little Children', u'Sarah Pierce', 0)
(5, u'Actress -- Supporting Role', u'Adriana Barraza', u'Babel', u'Amelia', 0)
(5, u'Actress -- Supporting Role', u'Cate Blanchett', u'Notes on a Scandal', u'Sheba Hart', 0)
(5, u'Actress -- Supporting Role', u'Abigail Breslin', u'Little Miss Sunshine', u'Olive', 0)
(5, u'Actress -- Supporting Role', u'Jennifer Hudson', u'Dreamgirls', u'Effie White', 1)
(5, u'Actress -- Supporting Role', u'Rinko Kikuchi', u'Babel', u'Chieko', 0)
(6, u'Actor -- Leading Role', u'Philip Seymour Hoffman', u'Capote', u'Truman Capote', 1)
(6, u'Actor -- Leading Role', u'Terrence Howard', u'Hustle & Flow', u'DJay', 0)
(6, u'Actor -- Leading Role', u'Heath Ledger', u'Brokeback Mountain', u'Ennis Del Mar', 0)
(6, u'Actor -- Leading Role', u'Joaquin Phoenix', u'Walk the Line', u'John R. Cash', 0)
(6, u'Actor -- Leading Role', u'David Strathairn', u'Good Night, and Good Luck.', u'Edward R. Murrow', 0)
(6, u'Actor -- Supporting Role', u'George Clooney', u'Syriana', u'Bob Barnes', 1)
(6, u'Actor -- Supporting Role', u'Matt Dillon', u'Crash', u'Officer Ryan', 0)
(6, u'Actor -- Supporting Role', u'Paul Giamatti', u'Cinderella Man', u'Joe Gould', 0)
(6, u'Actor -- Supporting Role', u'Jake Gyllenhaal', u'Brokeback Mountain', u'Jack Twist', 0)
(6, u'Actor -- Supporting Role', u'William Hurt', u'A History of Violence', u'Richie Cusack', 0)
(6, u'Actress -- Leading Role', u'Judi Dench', u'Mrs. Henderson Presents', u'Mrs. Laura Henderson', 0)
(6, u'Actress -- Leading Role', u'Felicity Huffman', u'Transamerica', u'Bree', 0)
(6, u'Actress -- Leading Role', u'Keira Knightley', u'Pride & Prejudice', u'Elizabeth Bennet', 0)
(6, u'Actress -- Leading Role', u'Charlize Theron', u'North Country', u'Josey Aimes', 0)
(6, u'Actress -- Leading Role', u'Reese Witherspoon', u'Walk the Line', u'June Carter', 1)
(6, u'Actress -- Supporting Role', u'Amy Adams', u'Junebug', u'Ashley', 0)
(6, u'Actress -- Supporting Role', u'Catherine Keener', u'Capote', u'Nelle Harper Lee', 0)
(6, u'Actress -- Supporting Role', u'Frances McDormand', u'North Country', u'Glory', 0)
(6, u'Actress -- Supporting Role', u'Rachel Weisz', u'The Constant Gardener', u'Tessa Quayle', 1)
(6, u'Actress -- Supporting Role', u'Michelle Williams', u'Brokeback Mountain', u'Alma', 0)
(7, u'Actor -- Leading Role', u'Don Cheadle', u'Hotel Rwanda', u'Paul Rusesabagina', 0)
(7, u'Actor -- Leading Role', u'Johnny Depp', u'Finding Neverland', u'Sir James Matthew Barrie', 0)
(7, u'Actor -- Leading Role', u'Leonardo DiCaprio', u'The Aviator', u'Howard Hughes', 0)
(7, u'Actor -- Leading Role', u'Clint Eastwood', u'Million Dollar Baby', u'Frankie Dunn', 0)
(7, u'Actor -- Leading Role', u'Jamie Foxx', u'Ray', u'Ray Charles', 1)
(7, u'Actor -- Supporting Role', u'Alan Alda', u'The Aviator', u'Senator Ralph Owen Brewster', 0)
(7, u'Actor -- Supporting Role', u'Thomas Haden Church', u'Sideways', u'Jack', 0)
(7, u'Actor -- Supporting Role', u'Jamie Foxx', u'Collateral', u'Max', 0)
(7, u'Actor -- Supporting Role', u'Morgan Freeman', u'Million Dollar Baby', u'Eddie Scrap-Iron Dupris', 1)
(7, u'Actor -- Supporting Role', u'Clive Owen', u'Closer', u'Larry', 0)
(7, u'Actress -- Leading Role', u'Annette Bening', u'Being Julia', u'Julia Lambert', 0)
(7, u'Actress -- Leading Role', u'Catalina Sandino Moreno', u'Maria Full of Grace', u'Maria', 0)
(7, u'Actress -- Leading Role', u'Imelda Staunton', u'Vera Drake', u'Vera', 0)
(7, u'Actress -- Leading Role', u'Hilary Swank', u'Million Dollar Baby', u'Maggie Fitzgerald', 1)
(7, u'Actress -- Leading Role', u'Kate Winslet', u'Eternal Sunshine of the Spotless Mind', u'Clementine Kruczynski', 0)
(7, u'Actress -- Supporting Role', u'Cate Blanchett', u'The Aviator', u'Katharine Hepburn', 1)
(7, u'Actress -- Supporting Role', u'Laura Linney', u'Kinsey', u'Clara McMillen', 0)
(7, u'Actress -- Supporting Role', u'Virginia Madsen', u'Sideways', u'Maya', 0)
(7, u'Actress -- Supporting Role', u'Sophie Okonedo', u'Hotel Rwanda', u'Tatiana Rusesabagina', 0)
(7, u'Actress -- Supporting Role', u'Natalie Portman', u'Closer', u'Alice', 0)
(8, u'Actor -- Leading Role', u'Johnny Depp', u'Pirates of the Caribbean: The Curse of the Black Pearl', u'Jack Sparrow', 0)
(8, u'Actor -- Leading Role', u'Ben Kingsley', u'House of Sand and Fog', u'Behrani', 0)
(8, u'Actor -- Leading Role', u'Jude Law', u'Cold Mountain', u'Inman', 0)
(8, u'Actor -- Leading Role', u'Bill Murray', u'Lost in Translation', u'Bob Harris', 0)
(8, u'Actor -- Leading Role', u'Sean Penn', u'Mystic River', u'Jimmy Markum', 1)
(8, u'Actor -- Supporting Role', u'Alec Baldwin', u'The Cooler', u'Shelly Kaplow', 0)
(8, u'Actor -- Supporting Role', u'Benicio Del Toro', u'21 Grams', u'Jack Jordan', 0)
(8, u'Actor -- Supporting Role', u'Djimon Hounsou', u'In America', u'Mateo', 0)
(8, u'Actor -- Supporting Role', u'Tim Robbins', u'Mystic River', u'Dave Boyle', 1)
(8, u'Actor -- Supporting Role', u'Ken Watanabe', u'The Last Samurai', u'Katsumoto', 0)
(8, u'Actress -- Leading Role', u'Keisha Castle-Hughes', u'Whale Rider', u'Paikea', 0)
(8, u'Actress -- Leading Role', u'Diane Keaton', u"Something's Gotta Give", u'Erica Barry', 0)
(8, u'Actress -- Leading Role', u'Samantha Morton', u'In America', u'Sarah', 0)
(8, u'Actress -- Leading Role', u'Charlize Theron', u'Monster', u'Aileen Wuornos', 1)
(8, u'Actress -- Leading Role', u'Naomi Watts', u'21 Grams', u'Cristina Peck', 0)
(8, u'Actress -- Supporting Role', u'Shohreh Aghdashloo', u'House of Sand and Fog', u'Nadi', 0)
(8, u'Actress -- Supporting Role', u'Patricia Clarkson', u'Pieces of April', u'Joy Burns', 0)
(8, u'Actress -- Supporting Role', u'Marcia Gay Harden', u'Mystic River', u'Celeste Boyle', 0)
(8, u'Actress -- Supporting Role', u'Holly Hunter', u'Thirteen', u'Melanie', 0)
(8, u'Actress -- Supporting Role', u'Ren\xe9e Zellweger', u'Cold Mountain', u'Ruby Thewes', 1)
(9, u'Actor -- Leading Role', u'Adrien Brody', u'The Pianist', u'Wladyslaw Szpilman', 1)
(9, u'Actor -- Leading Role', u'Nicolas Cage', u'Adaptation', u'Charlie Kaufman & Donald Kaufman', 0)
(9, u'Actor -- Leading Role', u'Michael Caine', u'The Quiet American', u'Thomas Fowler', 0)
(9, u'Actor -- Leading Role', u'Daniel Day-Lewis', u'Gangs of New York', u"Bill 'The Butcher' Cutting", 0)
(9, u'Actor -- Leading Role', u'Jack Nicholson', u'About Schmidt', u'Warren Schmidt', 0)
(9, u'Actor -- Supporting Role', u'Chris Cooper', u'Adaptation', u'John Laroche', 1)
(9, u'Actor -- Supporting Role', u'Ed Harris', u'The Hours', u'Richard Brown', 0)
(9, u'Actor -- Supporting Role', u'Paul Newman', u'Road to Perdition', u'John Rooney', 0)
(9, u'Actor -- Supporting Role', u'John C. Reilly', u'Chicago', u'Amos Hart', 0)
(9, u'Actor -- Supporting Role', u'Christopher Walken', u'Catch Me If You Can', u'Frank Abagnale', 0)
(9, u'Actress -- Leading Role', u'Salma Hayek', u'Frida', u'Frida Kahlo', 0)
(9, u'Actress -- Leading Role', u'Nicole Kidman', u'The Hours', u'Virginia Woolf', 1)
(9, u'Actress -- Leading Role', u'Diane Lane', u'Unfaithful', u'Connie Sumner', 0)
(9, u'Actress -- Leading Role', u'Julianne Moore', u'Far from Heaven', u'Cathy Whitaker', 0)
(9, u'Actress -- Leading Role', u'Ren\xe9e Zellweger', u'Chicago', u'Roxie Hart', 0)
(9, u'Actress -- Supporting Role', u'Kathy Bates', u'About Schmidt', u'Roberta Hertzel', 0)
(9, u'Actress -- Supporting Role', u'Julianne Moore', u'The Hours', u'Laura Brown', 0)
(9, u'Actress -- Supporting Role', u'Queen Latifah', u'Chicago', u'Matron Mama Morton', 0)
(9, u'Actress -- Supporting Role', u'Meryl Streep', u'Adaptation', u'Susan Orlean', 0)
(9, u'Actress -- Supporting Role', u'Catherine Zeta-Jones', u'Chicago', u'Velma Kelly', 1)
(10, u'Actor -- Leading Role', u'Russell Crowe', u'A Beautiful Mind', u'John Nash', 0)
(10, u'Actor -- Leading Role', u'Sean Penn', u'I Am Sam', u'Sam Dawson', 0)
(10, u'Actor -- Leading Role', u'Will Smith', u'Ali', u'Muhammad Ali', 0)
(10, u'Actor -- Leading Role', u'Denzel Washington', u'Training Day', u'Alonzo', 1)
(10, u'Actor -- Leading Role', u'Tom Wilkinson', u'In the Bedroom', u'Matt Fowler', 0)
(10, u'Actor -- Supporting Role', u'Jim Broadbent', u'Iris', u'John Bayley', 1)
(10, u'Actor -- Supporting Role', u'Ethan Hawke', u'Training Day', u'Jake', 0)
(10, u'Actor -- Supporting Role', u'Ben Kingsley', u'Sexy Beast', u'Don Logan', 0)
(10, u'Actor -- Supporting Role', u'Ian McKellen', u'The Lord of the Rings: The Fellowship of the Ring', u'Gandalf', 0)
(10, u'Actor -- Supporting Role', u'Jon Voight', u'Ali', u'Howard Cosell', 0)
(10, u'Actress -- Leading Role', u'Halle Berry', u"Monster's Ball", u'Leticia Musgrove', 1)
(10, u'Actress -- Leading Role', u'Judi Dench', u'Iris', u'Iris Murdoch', 0)
(10, u'Actress -- Leading Role', u'Nicole Kidman', u'Moulin Rouge', u'Satine', 0)
(10, u'Actress -- Leading Role', u'Sissy Spacek', u'In the Bedroom', u'Ruth Fowler', 0)
(10, u'Actress -- Leading Role', u'Ren\xe9e Zellweger', u"Bridget Jones's Diary", u'Bridget Jones', 0)
(10, u'Actress -- Supporting Role', u'Jennifer Connelly', u'A Beautiful Mind', u'Alicia Nash', 1)
(10, u'Actress -- Supporting Role', u'Helen Mirren', u'Gosford Park', u'Mrs. Wilson', 0)
(10, u'Actress -- Supporting Role', u'Maggie Smith', u'Gosford Park', u'Constance, Countess of Trentham', 0)
(10, u'Actress -- Supporting Role', u'Marisa Tomei', u'In the Bedroom', u'Natalie Strout', 0)
(10, u'Actress -- Supporting Role', u'Kate Winslet', u'Iris', u'Young Iris Murdoch', 0)
(11, u'Actor -- Leading Role', u'Javier Bardem', u'Before Night Falls', u'Reinaldo Arenas', 0)
(11, u'Actor -- Leading Role', u'Russell Crowe', u'Gladiator', u'Maximus Decimus Meridius', 1)
(11, u'Actor -- Leading Role', u'Tom Hanks', u'Cast Away', u'Chuck Noland', 0)
(11, u'Actor -- Leading Role', u'Ed Harris', u'Pollock', u'Jackson Pollock', 0)
(11, u'Actor -- Leading Role', u'Geoffrey Rush', u'Quills', u'The Marquis de Sade', 0)
(11, u'Actor -- Supporting Role', u'Jeff Bridges', u'The Contender', u'President Jackson Evans', 0)
(11, u'Actor -- Supporting Role', u'Willem Dafoe', u'Shadow of the Vampire', u'Max Schreck', 0)
(11, u'Actor -- Supporting Role', u'Benicio Del Toro', u'Traffic', u'Javier Rodriguez', 1)
(11, u'Actor -- Supporting Role', u'Albert Finney', u'Erin Brockovich', u'Ed Masry', 0)
(11, u'Actor -- Supporting Role', u'Joaquin Phoenix', u'Gladiator', u'Commodus', 0)
(11, u'Actress -- Leading Role', u'Joan Allen', u'The Contender', u'Laine Hanson', 0)
(11, u'Actress -- Leading Role', u'Juliette Binoche', u'Chocolat', u'Vianne Rocher', 0)
(11, u'Actress -- Leading Role', u'Ellen Burstyn', u'Requiem for a Dream', u'Sara Goldfarb', 0)
(11, u'Actress -- Leading Role', u'Laura Linney', u'You Can Count on Me', u"Samantha 'Sammy' Prescott", 0)
(11, u'Actress -- Leading Role', u'Julia Roberts', u'Erin Brockovich', u'Erin Brockovich', 1)
(11, u'Actress -- Supporting Role', u'Judi Dench', u'Chocolat', u'Armande Voizin', 0)
(11, u'Actress -- Supporting Role', u'Marcia Gay Harden', u'Pollock', u'Lee Krasner', 1)
(11, u'Actress -- Supporting Role', u'Kate Hudson', u'Almost Famous', u'Penny Lane', 0)
(11, u'Actress -- Supporting Role', u'Frances McDormand', u'Almost Famous', u'Elaine Miller', 0)
(11, u'Actress -- Supporting Role', u'Julie Walters', u'Billy Elliot', u'Mrs. Wilkinson', 0)
In [42]:
create_query = """CREATE TABLE nominations_two (
id integer PRIMARY KEY,
category text,
nominee text,
movie text,
character text,
won integer,
ceremony_id integer,
FOREIGN KEY(ceremony_id) REFERENCES ceremony(id)
);
"""
conn.execute(create_query)
Out[42]:
<sqlite3.Cursor at 0x7d3d730>
In [66]:
insert_query = "INSERT INTO nominations_two (ceremony_id, category, nominee, movie, character, won) VALUES (?, ?, ?, ?, ?, ?);"
conn.executemany(insert_query, results)
Out[66]:
<sqlite3.Cursor at 0x7ec20a0>
In [69]:
for row in conn.execute("SELECT * FROM nominations_two LIMIT 5;"):
print(row)
(1, u'Actor -- Leading Role', u'Javier Bardem', u'Biutiful', u'Uxbal', 0, 1)
(2, u'Actor -- Leading Role', u'Jeff Bridges', u'True Grit', u'Rooster Cogburn', 0, 1)
(3, u'Actor -- Leading Role', u'Jesse Eisenberg', u'The Social Network', u'Mark Zuckerberg', 0, 1)
(4, u'Actor -- Leading Role', u'Colin Firth', u"The King's Speech", u'King George VI', 1, 1)
(5, u'Actor -- Leading Role', u'James Franco', u'127 Hours', u'Aron Ralston', 0, 1)
In [74]:
# Drop and rename table
conn.execute("DROP TABLE nominations;")
conn.execute("ALTER TABLE nominations_two RENAME TO nominations;")
Out[74]:
<sqlite3.Cursor at 0x7ec2110>
In [77]:
conn.execute("SELECT * FROM nominations LIMIT 5;").fetchall()
Out[77]:
[(1, u'Actor -- Leading Role', u'Javier Bardem', u'Biutiful', u'Uxbal', 0, 1),
(2,
u'Actor -- Leading Role',
u'Jeff Bridges',
u'True Grit',
u'Rooster Cogburn',
0,
1),
(3,
u'Actor -- Leading Role',
u'Jesse Eisenberg',
u'The Social Network',
u'Mark Zuckerberg',
0,
1),
(4,
u'Actor -- Leading Role',
u'Colin Firth',
u"The King's Speech",
u'King George VI',
1,
1),
(5,
u'Actor -- Leading Role',
u'James Franco',
u'127 Hours',
u'Aron Ralston',
0,
1)]
In [78]:
conn.execute("""CREATE TABLE movies (
id integer PRIMARY KEY,
movie text);"""
)
conn.execute("""CREATE TABLE actors (
id integer PRIMARY KEY,
actor text);""")
conn.execute("""CREATE TABLE movies_actors (
id integer PRIMARY KEY,
movie_id integer,
actor_id integer,
FOREIGN KEY(movie_id) REFERENCES movie(id),
FOREIGN KEY(actor_id) REFERENCES actor(id));""")
Out[78]:
<sqlite3.Cursor at 0x7ec2500>
In [80]:
movies = conn.execute("SELECT DISTINCT movie FROM nominations").fetchall()
print(movies)
insert_query = "INSERT INTO movies (movie) VALUES (?);"
conn.executemany(insert_query, movies)
conn.execute("SELECT * FROM movies LIMIT 10;").fetchall()
[(u'127 Hours',), (u'21 Grams',), (u'A Beautiful Mind',), (u'A History of Violence',), (u'A Single Man',), (u'About Schmidt',), (u'Adaptation',), (u'Ali',), (u'Almost Famous',), (u'American Gangster',), (u'An Education',), (u'Animal Kingdom',), (u'Atonement',), (u'Away from Her',), (u'Babel',), (u'Before Night Falls',), (u'Being Julia',), (u'Billy Elliot',), (u'Biutiful',), (u'Black Swan',), (u'Blood Diamond',), (u'Blue Valentine',), (u"Bridget Jones's Diary",), (u'Brokeback Mountain',), (u'Capote',), (u'Cast Away',), (u'Catch Me If You Can',), (u'Changeling',), (u"Charlie Wilson's War",), (u'Chicago',), (u'Chocolat',), (u'Cinderella Man',), (u'Closer',), (u'Cold Mountain',), (u'Collateral',), (u'Crash',), (u'Crazy Heart',), (u'Doubt',), (u'Dreamgirls',), (u'Eastern Promises',), (u'Elizabeth: The Golden Age',), (u'Erin Brockovich',), (u'Eternal Sunshine of the Spotless Mind',), (u'Far from Heaven',), (u'Finding Neverland',), (u'Frida',), (u'Frost/Nixon',), (u'Frozen River',), (u'Gangs of New York',), (u'Gladiator',), (u'Gone Baby Gone',), (u'Good Night, and Good Luck.',), (u'Gosford Park',), (u'Half Nelson',), (u'Hotel Rwanda',), (u'House of Sand and Fog',), (u'Hustle & Flow',), (u'I Am Sam',), (u"I'm Not There",), (u'In America',), (u'In the Bedroom',), (u'In the Valley of Elah',), (u'Inglourious Basterds',), (u'Into the Wild',), (u'Invictus',), (u'Iris',), (u'Julie & Julia',), (u'Junebug',), (u'Juno',), (u'Kinsey',), (u'La Vie en Rose',), (u'Little Children',), (u'Little Miss Sunshine',), (u'Lost in Translation',), (u'Maria Full of Grace',), (u'Michael Clayton',), (u'Milk',), (u'Million Dollar Baby',), (u'Monster',), (u"Monster's Ball",), (u'Moulin Rouge',), (u'Mrs. Henderson Presents',), (u'Mystic River',), (u'Nine',), (u'No Country for Old Men',), (u'North Country',), (u'Notes on a Scandal',), (u'Pieces of April',), (u'Pirates of the Caribbean: The Curse of the Black Pearl',), (u'Pollock',), (u"Precious: Based on the Novel 'Push' by Sapphire",), (u'Pride & Prejudice',), (u'Quills',), (u'Rabbit Hole',), (u'Rachel Getting Married',), (u'Ray',), (u'Requiem for a Dream',), (u'Revolutionary Road',), (u'Road to Perdition',), (u'Sexy Beast',), (u'Shadow of the Vampire',), (u'Sideways',), (u"Something's Gotta Give",), (u'Sweeney Todd The Demon Barber of Fleet Street',), (u'Syriana',), (u'The Assassination of Jesse James by the Coward Robert Ford',), (u'The Aviator',), (u'The Blind Side',), (u'The Constant Gardener',), (u'The Contender',), (u'The Cooler',), (u'The Curious Case of Benjamin Button',), (u'The Dark Knight',), (u'The Departed',), (u'The Devil Wears Prada',), (u'The Fighter',), (u'The Hours',), (u'The Hurt Locker',), (u'The Kids Are All Right',), (u"The King's Speech",), (u'The Last King of Scotland',), (u'The Last Samurai',), (u'The Last Station',), (u'The Lord of the Rings: The Fellowship of the Ring',), (u'The Lovely Bones',), (u'The Messenger',), (u'The Pianist',), (u'The Pursuit of Happyness',), (u'The Queen',), (u'The Quiet American',), (u'The Reader',), (u'The Savages',), (u'The Social Network',), (u'The Town',), (u'The Visitor',), (u'The Wrestler',), (u'There Will Be Blood',), (u'Thirteen',), (u'Traffic',), (u'Training Day',), (u'Transamerica',), (u'Tropic Thunder',), (u'True Grit',), (u'Unfaithful',), (u'Up in the Air',), (u'Venus',), (u'Vera Drake',), (u'Vicky Cristina Barcelona',), (u'Volver',), (u'Walk the Line',), (u'Whale Rider',), (u"Winter's Bone",), (u'You Can Count on Me',)]
Out[80]:
[(1, u'127 Hours'),
(2, u'21 Grams'),
(3, u'A Beautiful Mind'),
(4, u'A History of Violence'),
(5, u'A Single Man'),
(6, u'About Schmidt'),
(7, u'Adaptation'),
(8, u'Ali'),
(9, u'Almost Famous'),
(10, u'American Gangster')]
In [81]:
actors = conn.execute("SELECT DISTINCT nominee FROM nominations").fetchall()
print(actors)
insert_query = "INSERT INTO actors (actor) VALUES (?);"
conn.executemany(insert_query, actors)
conn.execute("SELECT * FROM actors LIMIT 10;").fetchall()
[(u'Abigail Breslin',), (u'Adriana Barraza',), (u'Adrien Brody',), (u'Alan Alda',), (u'Alan Arkin',), (u'Albert Finney',), (u'Alec Baldwin',), (u'Amy Adams',), (u'Amy Ryan',), (u'Angelina Jolie',), (u'Anna Kendrick',), (u'Anne Hathaway',), (u'Annette Bening',), (u'Ben Kingsley',), (u'Benicio Del Toro',), (u'Bill Murray',), (u'Brad Pitt',), (u'Carey Mulligan',), (u'Casey Affleck',), (u'Catalina Sandino Moreno',), (u'Cate Blanchett',), (u'Catherine Keener',), (u'Catherine Zeta-Jones',), (u'Charlize Theron',), (u'Chris Cooper',), (u'Christian Bale',), (u'Christoph Waltz',), (u'Christopher Plummer',), (u'Christopher Walken',), (u'Clint Eastwood',), (u'Clive Owen',), (u'Colin Firth',), (u'Daniel Day-Lewis',), (u'David Strathairn',), (u'Denzel Washington',), (u'Diane Keaton',), (u'Diane Lane',), (u'Djimon Hounsou',), (u'Don Cheadle',), (u'Ed Harris',), (u'Eddie Murphy',), (u'Ellen Burstyn',), (u'Ellen Page',), (u'Ethan Hawke',), (u'Felicity Huffman',), (u'Forest Whitaker',), (u'Frances McDormand',), (u'Frank Langella',), (u'Gabourey Sidibe',), (u'Geoffrey Rush',), (u'George Clooney',), (u'Hailee Steinfeld',), (u'Hal Holbrook',), (u'Halle Berry',), (u'Heath Ledger',), (u'Helen Mirren',), (u'Helena Bonham Carter',), (u'Hilary Swank',), (u'Holly Hunter',), (u'Ian McKellen',), (u'Imelda Staunton',), (u'Jack Nicholson',), (u'Jacki Weaver',), (u'Jackie Earle Haley',), (u'Jake Gyllenhaal',), (u'James Franco',), (u'Jamie Foxx',), (u'Javier Bardem',), (u'Jeff Bridges',), (u'Jennifer Connelly',), (u'Jennifer Hudson',), (u'Jennifer Lawrence',), (u'Jeremy Renner',), (u'Jesse Eisenberg',), (u'Jim Broadbent',), (u'Joan Allen',), (u'Joaquin Phoenix',), (u'John C. Reilly',), (u'John Hawkes',), (u'Johnny Depp',), (u'Jon Voight',), (u'Josh Brolin',), (u'Jude Law',), (u'Judi Dench',), (u'Julia Roberts',), (u'Julianne Moore',), (u'Julie Christie',), (u'Julie Walters',), (u'Juliette Binoche',), (u'Kate Hudson',), (u'Kate Winslet',), (u'Kathy Bates',), (u'Keira Knightley',), (u'Keisha Castle-Hughes',), (u'Ken Watanabe',), (u'Laura Linney',), (u'Leonardo DiCaprio',), (u'Maggie Gyllenhaal',), (u'Maggie Smith',), (u'Marcia Gay Harden',), (u'Marion Cotillard',), (u'Marisa Tomei',), (u'Mark Ruffalo',), (u'Mark Wahlberg',), (u'Matt Damon',), (u'Matt Dillon',), (u'Melissa Leo',), (u'Meryl Streep',), (u'Michael Caine',), (u'Michael Shannon',), (u'Michelle Williams',), (u'Mickey Rourke',), (u"Mo'Nique",), (u'Morgan Freeman',), (u'Naomi Watts',), (u'Natalie Portman',), (u'Nicolas Cage',), (u'Nicole Kidman',), (u'Patricia Clarkson',), (u'Paul Giamatti',), (u'Paul Newman',), (u'Pen\xe9lope Cruz',), (u"Peter O'Toole",), (u'Philip Seymour Hoffman',), (u'Queen Latifah',), (u'Rachel Weisz',), (u'Reese Witherspoon',), (u'Ren\xe9e Zellweger',), (u'Richard Jenkins',), (u'Rinko Kikuchi',), (u'Robert Downey Jr.',), (u'Ruby Dee',), (u'Russell Crowe',), (u'Ryan Gosling',), (u'Salma Hayek',), (u'Samantha Morton',), (u'Sandra Bullock',), (u'Saoirse Ronan',), (u'Sean Penn',), (u'Shohreh Aghdashloo',), (u'Sissy Spacek',), (u'Sophie Okonedo',), (u'Stanley Tucci',), (u'Taraji P. Henson',), (u'Terrence Howard',), (u'Thomas Haden Church',), (u'Tilda Swinton',), (u'Tim Robbins',), (u'Tom Hanks',), (u'Tom Wilkinson',), (u'Tommy Lee Jones',), (u'Vera Farmiga',), (u'Viggo Mortensen',), (u'Viola Davis',), (u'Virginia Madsen',), (u'Will Smith',), (u'Willem Dafoe',), (u'William Hurt',), (u'Woody Harrelson',)]
Out[81]:
[(1, u'Abigail Breslin'),
(2, u'Adriana Barraza'),
(3, u'Adrien Brody'),
(4, u'Alan Alda'),
(5, u'Alan Arkin'),
(6, u'Albert Finney'),
(7, u'Alec Baldwin'),
(8, u'Amy Adams'),
(9, u'Amy Ryan'),
(10, u'Angelina Jolie')]
In [89]:
movie_actor = conn.execute("""
SELECT movies.id, actors.id
FROM movies, actors, nominations
WHERE movies.movie = nominations.movie
AND actors.actor = nominations.nominee""").fetchall()
# 220 rows
print(movie_actor)
insert_query = "INSERT INTO movies_actors (movie_id, actor_id) VALUES (?, ?);"
conn.executemany(insert_query, movie_actor)
conn.execute("SELECT * FROM movies_actors LIMIT 10;").fetchall()
[(1, 66), (2, 15), (2, 115), (3, 70), (3, 133), (4, 158), (5, 32), (6, 62), (6, 92), (7, 25), (7, 108), (7, 117), (8, 81), (8, 156), (9, 47), (9, 90), (10, 132), (11, 18), (12, 63), (13, 138), (14, 87), (15, 2), (15, 130), (16, 68), (17, 13), (18, 88), (19, 68), (20, 116), (21, 38), (21, 97), (22, 111), (23, 128), (24, 55), (24, 65), (24, 111), (25, 22), (25, 124), (26, 149), (27, 29), (28, 10), (29, 124), (30, 23), (30, 78), (30, 125), (30, 128), (31, 84), (31, 89), (32, 120), (33, 31), (33, 116), (34, 83), (34, 128), (35, 67), (36, 106), (37, 69), (37, 98), (38, 8), (38, 108), (38, 124), (38, 154), (39, 41), (39, 71), (40, 153), (41, 21), (42, 6), (42, 85), (43, 91), (44, 86), (45, 80), (46, 135), (47, 48), (48, 107), (49, 33), (50, 77), (50, 133), (51, 9), (52, 34), (53, 56), (53, 99), (54, 134), (55, 39), (55, 142), (56, 14), (56, 140), (57, 145), (58, 139), (59, 21), (60, 38), (60, 136), (61, 102), (61, 141), (61, 150), (62, 151), (63, 27), (64, 53), (65, 105), (65, 114), (66, 75), (66, 84), (66, 91), (67, 108), (68, 8), (69, 43), (70, 96), (71, 101), (72, 64), (72, 91), (73, 1), (73, 5), (74, 16), (75, 20), (76, 51), (76, 147), (76, 150), (77, 82), (77, 139), (78, 30), (78, 58), (78, 114), (79, 24), (80, 54), (81, 118), (82, 84), (83, 100), (83, 139), (83, 148), (84, 122), (85, 68), (86, 24), (86, 47), (87, 21), (87, 84), (88, 119), (89, 80), (90, 40), (90, 100), (91, 49), (91, 113), (92, 93), (93, 50), (94, 118), (95, 12), (96, 67), (97, 42), (98, 110), (99, 121), (100, 14), (101, 157), (102, 146), (102, 155), (103, 36), (104, 80), (105, 51), (106, 19), (107, 4), (107, 21), (107, 97), (108, 137), (109, 126), (110, 69), (110, 76), (111, 7), (112, 17), (112, 144), (113, 55), (114, 104), (115, 108), (116, 8), (116, 26), (116, 107), (117, 40), (117, 86), (117, 118), (118, 73), (119, 13), (119, 103), (120, 32), (120, 50), (120, 57), (121, 46), (122, 95), (123, 28), (123, 56), (124, 60), (125, 143), (126, 159), (127, 3), (128, 156), (129, 56), (130, 109), (131, 91), (132, 96), (133, 74), (134, 73), (135, 129), (136, 102), (136, 112), (137, 33), (138, 59), (139, 15), (140, 35), (140, 44), (141, 45), (142, 131), (143, 52), (143, 69), (144, 37), (145, 11), (145, 51), (145, 152), (146, 123), (147, 61), (148, 122), (149, 122), (150, 77), (150, 127), (151, 94), (152, 72), (152, 79), (153, 96)]
Out[89]:
[(1, 1, 66),
(2, 2, 15),
(3, 2, 115),
(4, 3, 70),
(5, 3, 133),
(6, 4, 158),
(7, 5, 32),
(8, 6, 62),
(9, 6, 92),
(10, 7, 25)]
In [90]:
conn.close()
In [ ]:
Content source: foxan/dataquest
Similar notebooks: